/**  
*  Doku2Buddy - A DokuWiki to BuddyPress Wiki-Component Converter
*  Copyright (C) 2010 Skye Book
*  
*  This program is free software: you can redistribute it and/or modify
*  it under the terms of the GNU General Public License as published by
*  the Free Software Foundation, either version 3 of the License, or
*  (at your option) any later version.
*  
*  This program is distributed in the hope that it will be useful,
*  but WITHOUT ANY WARRANTY; without even the implied warranty of
*  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
*  GNU General Public License for more details.
*  
*  You should have received a copy of the GNU General Public License
*  along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/
package com.sbook.d2b.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;

import com.sbook.d2b.formatting.BuddyPressArticle;

/**
 * @author Skye Book
 *
 */
public class DBManager {
	
	private Connection con = null;
	private PreparedStatement wpPostStatement = null;
	private PreparedStatement wpPostMetaStmt = null;
	private PreparedStatement bpGroupMetaFindGroupWikiEntryStmt = null;
	private PreparedStatement bpGroupMetaFindGroupWikiEntriesStmt = null;
	private PreparedStatement bpGroupMetaWikiInsertStmt = null;
	private PreparedStatement bpGetGroupsStmt = null;
	private PreparedStatement bpActivityInsert = null;
	private PreparedStatement checkGUIDStatement = null;
	private PreparedStatement findGroupIDWithSlug = null;
	private PreparedStatement findGroupIDWithName = null;
	private PreparedStatement findUserByName = null;
	private PreparedStatement findUserByEmail = null;
	private PreparedStatement findUserByID = null;
	private PreparedStatement updatePostGUID = null;
	private String wpURL;

	/**
	 * 
	 */
	public DBManager(String user, String pass, String dbName, String dbHost, int dbPort, String wpURL) {
		this.wpURL=wpURL;
		try {
			try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				con = DriverManager.getConnection("jdbc:mysql://"+dbHost+":"+dbPort+"/"+dbName,user,pass);
				wpPostStatement = con.prepareStatement("INSERT INTO `wp_posts` (`post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES" +
						"(?, ?, ?, ?, ?, 'Excerpt Goes Here', 'publish', 'open', 'open', '', ?, '', '', ?, ?, '', 0, ?, ?, 'wiki', '', 0)", new String[]{"ID"});
				
				wpPostMetaStmt = con.prepareStatement("INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (?, ?, ?)");
				
				bpGroupMetaFindGroupWikiEntryStmt = con.prepareStatement("SELECT " + WPConstants.GROUP_META_ENTRY_ID + " FROM " + WPConstants.GROUP_META_TABLE() + " WHERE " + WPConstants.GROUP_META_GROUP_ID + " = ? AND " + WPConstants.GROUP_META_KEY + " LIKE " + WPConstants.GROUP_WIKI_ENTRY_ID_META_KEY);
				
				bpGroupMetaFindGroupWikiEntriesStmt = con.prepareStatement("SELECT " + WPConstants.GROUP_META_VALUE + " FROM " + WPConstants.GROUP_META_TABLE() + " WHERE " + WPConstants.GROUP_META_GROUP_ID + " = ? AND " + WPConstants.GROUP_META_KEY + " LIKE " + WPConstants.GROUP_WIKI_ENTRY_ID_META_KEY);
				
				bpGroupMetaWikiInsertStmt = con.prepareStatement("UPDATE " + WPConstants.GROUP_META_TABLE() + " SET " + WPConstants.GROUP_META_VALUE + " = ? WHERE " + WPConstants.GROUP_META_ENTRY_ID + " = ?;");
				
				//bpGetGroupsStmt = con.prepareStatement("SELECT * FROM " + WPConstants.BP_GROUPS_TABLE());
				bpGetGroupsStmt = con.prepareStatement("SELECT * FROM  `wp_bp_groups`");
				
				bpActivityInsert = con.prepareStatement("INSERT INTO " + WPConstants.BP_ACTIVITY_TABLE() + " (" + WPConstants.BP_ACTIVITY_USER_ID + ", " + WPConstants.BP_ACTIVITY_COMPONENT + "," + WPConstants.BP_ACTIVITY_TYPE + ", " + WPConstants.BP_ACTIVITY_ACTION + ", " + WPConstants.BP_ACTIVITY_CONTENT +
						", " + WPConstants.BP_ACTIVITY_PRMRY_LNK + ", " + WPConstants.BP_ACTIVITY_ITEM_ID + ", " + WPConstants.BP_ACTIVITY_SECONDARY_ITEM_ID + ", " + WPConstants.BP_ACTIVITY_DATE_RECORDED + ", " + WPConstants.BP_ACTIVITY_HIDE_SITEWIDE + ", " + WPConstants.BP_ACTIVITY_MPTT_L +
						", " + WPConstants.BP_ACTIVITY_MPTT_R + ") VALUES "+
						"(?,?,?,?,?,?,?,?,?,?,?,?)");
				
				updatePostGUID = con.prepareStatement("UPDATE `wp_posts` SET `guid` = ? WHERE `ID` = ?");
				
				checkGUIDStatement = con.prepareStatement("SELECT * FROM `wp_posts` WHERE `guid` LIKE ?");
				
				findGroupIDWithSlug = con.prepareStatement("SELECT `id` FROM `wp_bp_groups` WHERE `slug` LIKE ?");
				
				findGroupIDWithName = con.prepareStatement("SELECT `id` FROM `wp_bp_groups` WHERE `name` LIKE ?");
				
				findUserByName = con.prepareStatement("SELECT `ID` FROM `wp_users` WHERE `user_login` = ?");
				
				findUserByEmail = con.prepareStatement("SELECT `ID` FROM `wp_users` WHERE `user_email` = ?");
				
				findUserByID = con.prepareStatement("SELECT `user_login` FROM `wp_users` WHERE `ID` = ?");
				
				/*
				 * INSERT INTO `wp_posts` (`post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES 
(1, '2010-05-22 21:46:38', '2010-05-22 21:46:38', '<p><strong>yes!</strong></p> <p>this is my first page</p> <p> </p> <p>isnt it great!</p>', 'my first page', 'yes! this is my first page   isnt it great!', 'publish', 'open', 'open', '', '1-p', '', '', '2010-05-22 21:46:38', '2010-05-22 21:46:38', '', 0, 'http://jmonkeyengine.bitwaves.de/wordpress/2010/05/22/1-p/', 2, 'wiki', '', 0);
				 */
			} catch (ClassNotFoundException e) {
				System.err.println("Check your classpath: " + e.getMessage());
			} catch (InstantiationException e) {
				System.err.println("Check com.mysql.jdbc.Driver (likely a classpath problem)");
			} catch (IllegalAccessException e) {
				System.err.println("IllegalAccessException: " + e.getMessage());
			}
		} catch (SQLException e) {
			System.err.println("SQLException: " + e.getMessage());
			System.err.println("SQLState: " + e.getSQLState());
			System.err.println("VendorError: " + e.getErrorCode());
		}
	}
	
	public int insertArticle(BuddyPressArticle article){
		try {
			wpPostStatement.setInt(1, article.getUserID());
			
			wpPostStatement.setDate(2, article.getSqlDate());
			wpPostStatement.setDate(3, article.getSqlDate());
			
			
			wpPostStatement.setString(4, article.getMarkup());
			wpPostStatement.setString(5, article.getTitle());
			
			
			wpPostStatement.setString(6, article.getPostName());
			
			wpPostStatement.setDate(7, article.getSqlDate());
			wpPostStatement.setDate(8, article.getSqlDate());
			
			String guid = createGUID(article.getSqlDate(), article.getGroupID(), article.getArticleSlug());
			
			//wpPostStatement.setString(9, "http://localhost/wordpress/?p=");
			wpPostStatement.setString(9, guid);
			
			wpPostStatement.setInt(10, 2);
			
			
			wpPostStatement.executeUpdate();
			
			ResultSet rs = wpPostStatement.getGeneratedKeys();
			int postID=-1;
			if(rs.first()){
				postID = rs.getInt(1);
				wpPostMetaStmt.setInt(1, postID);
				wpPostMetaStmt.setString(2, "wiki_view_access");
				wpPostMetaStmt.setString(3, "public");
				wpPostMetaStmt.executeUpdate();
				wpPostMetaStmt.setString(2, "wiki_edit_access");
				wpPostMetaStmt.setString(3, "all-members");
				wpPostMetaStmt.executeUpdate();
				wpPostMetaStmt.setString(2, "wiki_page_visible");
				wpPostMetaStmt.setString(3, "yes");
				wpPostMetaStmt.executeUpdate();
			}
			
			// create bp activity entry
			bpActivityInsert.setInt(1, article.getUserID());
			bpActivityInsert.setString(2, "groups");
			bpActivityInsert.setString(3, "wiki_group_page_create");
			
			// TODO: create action text (generate userlink)
			bpActivityInsert.setString(4, createActivityString(article.getAuthor(), guid, article.getTitle(), article.getGroupName()));

			bpActivityInsert.setString(5, "");
			bpActivityInsert.setString(6, guid);
			bpActivityInsert.setInt(7, article.getGroupID());
			bpActivityInsert.setInt(8, postID);
			bpActivityInsert.setDate(9, article.getSqlDate());
			bpActivityInsert.setInt(10, 0);
			bpActivityInsert.setInt(11, 0);
			bpActivityInsert.setInt(12, 0);
			
			bpActivityInsert.executeUpdate();
			
			return postID;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	private String createActivityString(String username, String guid, String articlename, String groupname){
		String activity = "<a href=\""+generateUserURL(username)+ "\">"+username+"</a> created the <a href=\""+guid +"\">"+articlename+"</a> page in the group <a href=\""+generategroupURL(groupname)+"\">"+groupname+"</a>";
		
		return activity;
	}
	
	@SuppressWarnings("deprecation")
	private String createGUID(Date date, int articleGroup, String slug){
		
		int numIncrementDigits=1;
		boolean result=false;
		boolean firstPass=true;
		String checkedGUID = wpURL+"/"+(date.getYear()+1900)+"/"+(date.getMonth()+1)+"/"+articleGroup+"-"+slug;
		while(!result){
			
			result=checkGUID(checkedGUID);
			if(!result){
				if(firstPass){
					checkedGUID=checkedGUID+"_1";
				}
				else{
					int increment = Integer.parseInt(checkedGUID.substring(checkedGUID.length()-(numIncrementDigits), checkedGUID.length()-1));
					if(increment == 9){
						numIncrementDigits++;
					}
					checkedGUID=checkedGUID.substring(0, checkedGUID.length()-numIncrementDigits)+(increment+1);
				}
			}
			firstPass=false;
		}
		return checkedGUID;
	}
	
	/**
	 * Find a user in the database by their user name.
	 * @param userName The name the user uses to login.
	 * @return The user's ID, or -1 if the user does not exist,
	 * or -2 if there was an error.
	 */
	public int findUserViaName(String userName){
		try {
			findUserByName.setString(1, userName);
			ResultSet rs = findUserByName.executeQuery();
			if(rs.first()){
				return rs.getInt("ID");
			}
			else return -1;
		} catch (SQLException e) {
			e.printStackTrace();
			return -2;
		}
		
	}
	
	/**
	 * Find a user in the database by their email address.
	 * @param email The email address of the user.
	 * @return The user's ID, or -1 if the user does not exist,
	 * or -2 if there was an error.
	 */
	public int findUserViaEmail(String email){
		try {
			findUserByEmail.setString(1, email);
			ResultSet rs = findUserByEmail.executeQuery();
			if(rs.first()){
				return rs.getInt("ID");
			}
			else return -1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -2;
	}
	
	private boolean checkGUID(String guidToCheck){
		try {
			checkGUIDStatement.setString(1, guidToCheck);
			ResultSet rs = checkGUIDStatement.executeQuery();
			if(rs.first()){
				return false;
			}
			else return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * unstable
	 * @param groupSlug
	 * @return
	 */
	public int findGroupIDBySlug(String groupSlug){
		try {
			findGroupIDWithSlug.setString(1, groupSlug);
			ResultSet rs = findGroupIDWithSlug.executeQuery();
			if(rs.first()){
				return rs.getInt("id");
			}
			else return 0;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	/**
	 * Finds a group's ID by searching for its name.
	 * @param groupName The group's name (with spaces and other character's included)
	 * @return
	 */
	public int findGroupIDByName(String groupName){
		try {
			findGroupIDWithSlug.setString(1, groupName);
			ResultSet rs = findGroupIDWithSlug.executeQuery();
			if(rs.first()){
				return rs.getInt("id");
			}
			else return 0;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	/**
	 * Find the user name by their user ID.
	 * @param ID The user's ID.
	 * @return The user's login name, "NOT_FOUND" if the ID does not exist,
	 * or "SQL_ERROR" if and SQL exception occurred.
	 */
	public String findUserByID(int ID){
		try {
			findUserByID.setInt(1, ID);
			ResultSet rs = findUserByID.executeQuery();
			if(rs.first()){
				return rs.getString("user_login");
			}
			else return "NOT_FOUND";
		} catch (SQLException e) {
			e.printStackTrace();
			return "SQL_ERROR";
		}
	}
	
	/**
	 * Finds the index where the group's wiki page ID's are stored
	 * in the groupsmeta table.
	 * @param groupID The groups ID number.
	 * @return
	 */
	private int findGroupWikiEntryIndex(int groupID){
		try {
			bpGroupMetaFindGroupWikiEntryStmt.setInt(1, groupID);
			ResultSet rs = bpGroupMetaFindGroupWikiEntryStmt.executeQuery();
			if(rs.first()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	/**
	 * Finds the index where the group's wiki page ID's are stored
	 * in the groupsmeta table.
	 * @param groupID The groups ID number.
	 * @return
	 */
	private int findGroupWikiEntryIndex(String groupName){
		return findGroupWikiEntryIndex(findGroupID(groupName));
	}
	
	private int findGroupID(String groupName){
		try {
			ResultSet rs = con.createStatement().executeQuery("SELECT id from " + WPConstants.BP_GROUPS_TABLE() + " WHERE name = '" + groupName + "';");
			if(rs.first()){
				int groupID = rs.getInt("id");
				System.out.println(groupName + " ID: " + groupID);
				return groupID;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	private ArrayList<Integer> findPreExistingGroupWikiEntries(int groupIndex){
		try {
			bpGroupMetaFindGroupWikiEntriesStmt.setInt(1, groupIndex);
			ResultSet rs = bpGroupMetaFindGroupWikiEntriesStmt.executeQuery();
			if(rs.first()){
				String entry = rs.getString(WPConstants.GROUP_META_VALUE);
				boolean isPageID = false;
				ArrayList<Integer> entries = new ArrayList<Integer>();
				entry=entry.substring(entry.indexOf("{"));
				while(entry.contains("i:")){
					// cut off "i:"
					entry.substring(2);
					
					// find number in the string
					int currentNumber=0;
					boolean numberCreated = false;
					int indicesToCut=1;
					while(!numberCreated){
						try{
							currentNumber = Integer.parseInt(entry.substring(0, indicesToCut));
							indicesToCut++;
						}
						catch(NumberFormatException e){
							numberCreated=true;
						}
					}
					
					// add the pageID and make the switch between pageID and wiki entry index
					if(isPageID){
						if(currentNumber!=0){
							entries.add(currentNumber);
						}
						isPageID=false;
					}
					else isPageID=true;
					
					// cut off this number
					entry.substring(indicesToCut);
				}
				
				return entries;
			}
			else return null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Updates the group's wiki entries.
	 * @param groupID
	 * @param newPageIDList
	 */
	public void updateGroupWikiEntries(String bpGroup, ArrayList<Integer> newPageIDList){
		ArrayList<Integer> pageIDList = null;
		int groupEntryIndex = findGroupWikiEntryIndex(bpGroup);
		System.out.println("Wiki array index");
		
		// find the pre-existing entries
		pageIDList = findPreExistingGroupWikiEntries(groupEntryIndex);
		
		// if no entries were found, set the incoming page list, or else add them.
		if(pageIDList==null){
			pageIDList = newPageIDList;
		}
		else{
			pageIDList.addAll(newPageIDList);
		}
		
		
		// create the entry
		String entry = "a:" +pageIDList.size()+":{";
		int currentEntry=0;
		Iterator<Integer> it = pageIDList.iterator();
		while(it.hasNext()){
			entry+="i:"+currentEntry+";"+"i:"+it.next()+";";
			currentEntry++;
		}
		entry+="}";
		
		// perform the update
		if(groupEntryIndex!=0){
			try {
				bpGroupMetaWikiInsertStmt.setString(1, entry);
				bpGroupMetaWikiInsertStmt.setInt(2, groupEntryIndex);
				
				bpGroupMetaWikiInsertStmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public ArrayList<String> getAllGroups(){
		try {
			//ResultSet rs = con.createStatement().executeQuery("SELECT name from wp_bp_groups");
			ResultSet rs = bpGetGroupsStmt.executeQuery();
			ArrayList<String> names = new ArrayList<String>();
			while(rs.next()){
				names.add(rs.getString(WPConstants.GROUP_NAME));
			}
			
			return names;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	private String generateUserURL(String username){
		return wpURL+"/members/" + username+"/";
	}
	
	private String generategroupURL(String groupname){
		return wpURL+"/groups/" + groupname+"/";
	}
}
